﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using CoreLab.Oracle; //Devart.Data.Oracle; //using Oracle.DataAccess.Client;
using System.Web;
using System.Web.Caching;


using LMD.Constant;
using LMD.Lib.Oracle;

namespace Cached
{
    /// <summary>
    /// Summary description for CompanyReport_PenyebabGangguanTable
    /// </summary>
    public class CompanyReport_PenyebabGangguanTable : CacheAbstraction
    {
        public DataTable GetPenyebabGangguanRekap(int iCompanyId, DateTime dtmStart, DateTime dtmEnd)
        {
            return GetPenyebabGangguanRekap(iCompanyId, dtmStart, dtmEnd, false);
        }
        public DataTable GetPenyebabGangguanRekap(int iCompanyId, DateTime dtmStart, DateTime dtmEnd, bool bForceRefresh)
        {
            String sxCacheKey = String.Format("{0}{1}_{2}_{3}",
                KCache.V_REPORT_PENYEBAB_GANGGUAN_REKAP, 
                iCompanyId.ToString(),
                dtmStart.ToShortDateString(),
                dtmEnd.ToShortDateString());

            if (bForceRefresh)
            {
                HttpRuntime.Cache.Remove(sxCacheKey);
            }

            if (HttpRuntime.Cache[sxCacheKey] == null)
            {
                DBLib dbl = new DBLib(Connection.WEB_USER);

                List<OracleParameter> lsParams = new List<OracleParameter>();

                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmID";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
                lsParams[lsParams.Count - 1].Value = iCompanyId;

                //lsParams.Add(new OracleParameter());
                //lsParams[lsParams.Count - 1].ParameterName = "prmStart";
                //lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Date;
                //lsParams[lsParams.Count - 1].Value = dtmStart;

                //lsParams.Add(new OracleParameter());
                //lsParams[lsParams.Count - 1].ParameterName = "prmEnd";
                //lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Date;
                //lsParams[lsParams.Count - 1].Value = dtmEnd;

                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmStartMonth";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
                lsParams[lsParams.Count - 1].Value = dtmStart.Month;

                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmStartYear";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
                lsParams[lsParams.Count - 1].Value = dtmStart.Year;

                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmEndMonth";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
                lsParams[lsParams.Count - 1].Value = dtmEnd.Month;

                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmEndYear";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
                lsParams[lsParams.Count - 1].Value = dtmEnd.Year;

                String sxQuery =
                    @"
                      SELECT   --COMPANY_ID,
                               --COMPANY_NAME,
                               --NETWORK_NO,
                               COUNT (table2.SEBAB) TOTAL_SEBAB,
                               table2.sebab,
                               table2.MONTH_NUM,
                               table2.YEAR_NUM
                        FROM   (  SELECT   m2.COMPANY_ID,
                                           comp.COMPANY_NAME,
                                           CAST (TO_CHAR (t1.RECORD_DATE, 'MM') AS number) MONTH_NUM,
                                           CAST (TO_CHAR (t1.RECORD_DATE, 'yyyy') AS number) YEAR_NUM,
                                           t1.RECORD_DATE,
                                           net.NETWORK_NO,
                                           v1.PERANGKAT,
                                           v1.BAGIAN_PERANGKAT,
                                           v1.PENYEBAB,
                                              v1.PERANGKAT
                                           --|| '-'
                                           --|| v1.BAGIAN_PERANGKAT
                                           --|| '-'
                                           --|| v1.PENYEBAB
                                              AS sebab
                                    FROM                  sni.T_PRE_TROUBLE_REC t1
                                                       LEFT JOIN
                                                          sni.NETWORK net
                                                       ON t1.NETWORK_ID = net.NETWORK_ID
                                                    LEFT JOIN
                                                       sni.NETWORK_SITE m1
                                                    ON t1.NETWORK_ID = m1.NETWORK_ID
                                                 LEFT JOIN
                                                    sni.SITE m2
                                                 ON m1.SITE_ID = m2.SITE_ID
                                              JOIN
                                                 sni.v_crm_ver_sebab_solusi v1
                                              ON t1.T_PRE_TROUBLE_REC_ID = v1.T_PRE_TROUBLE_REC_ID
                                           LEFT JOIN
                                              sni.COMPANY comp
                                           ON m2.COMPANY_ID = comp.COMPANY_ID
                                   WHERE   m1.p_network_site_status_id = 4
                                           AND NVL (m1.valid_to, SYSDATE + 1) > SYSDATE
                                ORDER BY   YEAR_NUM DESC, MONTH_NUM DESC) table2
                       WHERE       TO_DATE ('1\' || :prmStartMonth || '\' || :prmStartYear,
                                            'dd\mm\yyyy') <= RECORD_DATE
                               AND ADD_MONTHS (
                                     TO_DATE ('1\' || :prmEndMonth || '\' || :prmEndYear,
                                              'dd\mm\yyyy'),
                                     1
                                  ) > RECORD_DATE
                               AND table2.COMPANY_ID = :prmID
                    GROUP BY   --COMPANY_ID,
                               --COMPANY_NAME,
                               table2.MONTH_NUM,
                               table2.YEAR_NUM,
                               table2.sebab
                    --NETWORK_NO
                    ORDER BY   table2.YEAR_NUM DESC, table2.MONTH_NUM DESC
                    ";

                DataTable dt = dbl.GetDataTable(sxQuery, lsParams.ToArray());

                AddToCache(sxCacheKey, dt, CacheType.Daily);
                
            }
            return (DataTable)HttpRuntime.Cache[sxCacheKey];
        }

        public DataTable GetPenyebabGangguanDetail(int iCompanyId, DateTime dtmStart, DateTime dtmEnd)
        {
            return GetPenyebabGangguanDetail(iCompanyId, dtmStart, dtmEnd, false);
        }
        public DataTable GetPenyebabGangguanDetail(int iCompanyId, DateTime dtmStart, DateTime dtmEnd, bool bForceRefresh)
        {
            String sxCacheKey = String.Format("{0}{1}_{2}_{3}",
                KCache.V_REPORT_PENYEBAB_GANGGUAN_DETAIL,
                iCompanyId.ToString(),
                dtmStart.ToShortDateString(),
                dtmEnd.ToShortDateString());

            if(bForceRefresh)
            {
                HttpRuntime.Cache.Remove(sxCacheKey);
            }

            if (HttpRuntime.Cache[sxCacheKey] == null)
            {
                DBLib dbl = new DBLib(Connection.WEB_USER);

                List<OracleParameter> lsParams = new List<OracleParameter>();

                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmID";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
                lsParams[lsParams.Count - 1].Value = iCompanyId;

                //lsParams.Add(new OracleParameter());
                //lsParams[lsParams.Count - 1].ParameterName = "prmStart";
                //lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Date;
                //lsParams[lsParams.Count - 1].Value = dtmStart;

                //lsParams.Add(new OracleParameter());
                //lsParams[lsParams.Count - 1].ParameterName = "prmEnd";
                //lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Date;
                //lsParams[lsParams.Count - 1].Value = dtmEnd;

                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmStartMonth";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
                lsParams[lsParams.Count - 1].Value = dtmStart.Month;

                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmStartYear";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
                lsParams[lsParams.Count - 1].Value = dtmStart.Year;

                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmEndMonth";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
                lsParams[lsParams.Count - 1].Value = dtmEnd.Month;

                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmEndYear";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
                lsParams[lsParams.Count - 1].Value = dtmEnd.Year;

                String sxQuery =
                    @"
                      SELECT   table1.COMPANY_NO,
                               table1.COMPANY_ID,
                               table1.COMPANY_NAME,
                               table1.MONTH_NUM,
                               table1.YEAR_NUM,
                               table1.NETWORK_NO,
                               table1.sebab,
                               COUNT (table1.SEBAB) TOTAL_SEBAB
                        FROM   (  SELECT   comp.COMPANY_NO,
                                           m2.COMPANY_ID,
                                           comp.COMPANY_NAME,
                                           CAST (TO_CHAR (t1.RECORD_DATE, 'MM') AS number) MONTH_NUM,
                                           CAST (TO_CHAR (t1.RECORD_DATE, 'yyyy') AS number) YEAR_NUM,
                                           t1.RECORD_DATE,
                                           net.NETWORK_NO,
                                           v1.PERANGKAT,
                                           v1.BAGIAN_PERANGKAT,
                                           v1.PENYEBAB,
                                              v1.PERANGKAT
                                           --|| '-'
                                           --|| v1.BAGIAN_PERANGKAT
                                           --|| '-'
                                           --|| v1.PENYEBAB
                                              AS sebab
                                    FROM                  sni.T_PRE_TROUBLE_REC t1
                                                       LEFT JOIN
                                                          sni.NETWORK net
                                                       ON t1.NETWORK_ID = net.NETWORK_ID
                                                    LEFT JOIN
                                                       sni.NETWORK_SITE m1
                                                    ON t1.NETWORK_ID = m1.NETWORK_ID
                                                 LEFT JOIN
                                                    sni.SITE m2
                                                 ON m1.SITE_ID = m2.SITE_ID
                                              JOIN
                                                 sni.v_crm_ver_sebab_solusi v1
                                              ON t1.T_PRE_TROUBLE_REC_ID = v1.T_PRE_TROUBLE_REC_ID
                                           LEFT JOIN
                                              sni.COMPANY comp
                                           ON m2.COMPANY_ID = comp.COMPANY_ID
                                   WHERE   m1.p_network_site_status_id = 4
                                           AND NVL (m1.valid_to, SYSDATE + 1) > SYSDATE
                                ORDER BY   YEAR_NUM DESC, MONTH_NUM DESC) table1
                       WHERE       TO_DATE ('1\' || :prmStartMonth || '\' || :prmStartYear,
                                            'dd\mm\yyyy') <= RECORD_DATE
                               AND ADD_MONTHS (
                                     TO_DATE ('1\' || :prmEndMonth || '\' || :prmEndYear,
                                              'dd\mm\yyyy'),
                                     1
                                  ) > RECORD_DATE
                               AND table1.COMPANY_ID = :prmID
                    GROUP BY   table1.COMPANY_NO,
                               table1.COMPANY_ID,
                               table1.COMPANY_NAME,
                               table1.MONTH_NUM,
                               table1.YEAR_NUM,
                               table1.sebab,
                               table1.NETWORK_NO
                    ORDER BY   table1.YEAR_NUM DESC, table1.MONTH_NUM DESC
                    ";

                DataTable dt = dbl.GetDataTable(sxQuery, lsParams.ToArray());

                AddToCache(sxCacheKey, dt, CacheType.Daily);
                
            }
            return (DataTable)HttpRuntime.Cache[sxCacheKey];
        }

        public DataTable GetPerangkatSeries(int iCompanyId, DateTime dtmStart, DateTime dtmEnd)
        {
            return GetPerangkatSeries(iCompanyId, dtmStart, dtmEnd, false);
        }
        public DataTable GetPerangkatSeries(int iCompanyId, DateTime dtmStart, DateTime dtmEnd, bool bForceRefresh)
        {
            String sxCacheKey = String.Format("{0}{1}_{2}_{3}",
                KCache.V_REPORT_PERANGKAT_SERIES,
                iCompanyId.ToString(),
                dtmStart.ToShortDateString(),
                dtmEnd.ToShortDateString());

            if (bForceRefresh)
            {
                HttpRuntime.Cache.Remove(sxCacheKey);
            }

            if (HttpRuntime.Cache[sxCacheKey] == null)
            {
                DBLib dbl = new DBLib(Connection.WEB_USER);

                List<OracleParameter> lsParams = new List<OracleParameter>();

                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmID";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
                lsParams[lsParams.Count - 1].Value = iCompanyId;

                //lsParams.Add(new OracleParameter());
                //lsParams[lsParams.Count - 1].ParameterName = "prmStart";
                //lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Date;
                //lsParams[lsParams.Count - 1].Value = dtmStart;

                //lsParams.Add(new OracleParameter());
                //lsParams[lsParams.Count - 1].ParameterName = "prmEnd";
                //lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Date;
                //lsParams[lsParams.Count - 1].Value = dtmEnd;

                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmStartMonth";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
                lsParams[lsParams.Count - 1].Value = dtmStart.Month;

                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmStartYear";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
                lsParams[lsParams.Count - 1].Value = dtmStart.Year;

                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmEndMonth";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
                lsParams[lsParams.Count - 1].Value = dtmEnd.Month;

                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmEndYear";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
                lsParams[lsParams.Count - 1].Value = dtmEnd.Year;

                String sxQuery =
                    @"
                      
                        SELECT   DISTINCT
                                 v1.PERANGKAT --|| '-' || v1.BAGIAN_PERANGKAT || '-' || v1.PENYEBAB
                                    AS sebab
                          FROM                  sni.T_PRE_TROUBLE_REC t1
                                             LEFT JOIN
                                                sni.NETWORK net
                                             ON t1.NETWORK_ID = net.NETWORK_ID
                                          LEFT JOIN
                                             sni.NETWORK_SITE m1
                                          ON t1.NETWORK_ID = m1.NETWORK_ID
                                       LEFT JOIN
                                          sni.SITE m2
                                       ON m1.SITE_ID = m2.SITE_ID
                                    JOIN
                                       sni.v_crm_ver_sebab_solusi v1
                                    ON t1.T_PRE_TROUBLE_REC_ID = v1.T_PRE_TROUBLE_REC_ID
                                 LEFT JOIN
                                    sni.COMPANY comp
                                 ON m2.COMPANY_ID = comp.COMPANY_ID
                         WHERE       m1.p_network_site_status_id = 4
                                 AND NVL (m1.valid_to, SYSDATE + 1) > SYSDATE
                                 AND TO_DATE ('1\' || :prmStartMonth || '\' || :prmStartYear,
                                              'dd\mm\yyyy') <= RECORD_DATE
                                 AND ADD_MONTHS (
                                       TO_DATE ('1\' || :prmEndMonth || '\' || :prmEndYear,
                                                'dd\mm\yyyy'),
                                       1
                                    ) > RECORD_DATE
                                 AND COMPANY_ID = :prmID  
                    ";

                DataTable dt = dbl.GetDataTable(sxQuery, lsParams.ToArray());

                AddToCache(sxCacheKey, dt, CacheType.Daily);

            }
            return (DataTable)HttpRuntime.Cache[sxCacheKey];
        }        

		public DataTable GetPerangkatFilterSeries(int iCompanyId)
        {
            return GetPerangkatFilterSeries(iCompanyId, false);
        }
        public DataTable GetPerangkatFilterSeries(int iCompanyId, bool bForceRefresh)
        {
            String sxCacheKey = String.Format("{0}{1}",
                KCache.V_REPORT_PERANGKAT_FILTER_SERIES,
                iCompanyId.ToString());

            if (bForceRefresh)
            {
                HttpRuntime.Cache.Remove(sxCacheKey);
            }

            if (HttpRuntime.Cache[sxCacheKey] == null)
            {
                DBLib dbl = new DBLib(Connection.WEB_USER);

                String sxQuery =
                    @"
                      
                        SELECT   DISTINCT
                                 v1.PERANGKAT --|| '-' || v1.BAGIAN_PERANGKAT || '-' || v1.PENYEBAB
                                    AS sebab
                          FROM                  sni.T_PRE_TROUBLE_REC t1
                                             LEFT JOIN
                                                sni.NETWORK net
                                             ON t1.NETWORK_ID = net.NETWORK_ID
                                          LEFT JOIN
                                             sni.NETWORK_SITE m1
                                          ON t1.NETWORK_ID = m1.NETWORK_ID
                                       LEFT JOIN
                                          sni.SITE m2
                                       ON m1.SITE_ID = m2.SITE_ID
                                    JOIN
                                       sni.v_crm_ver_sebab_solusi v1
                                    ON t1.T_PRE_TROUBLE_REC_ID = v1.T_PRE_TROUBLE_REC_ID
                                 LEFT JOIN
                                    sni.COMPANY comp
                                 ON m2.COMPANY_ID = comp.COMPANY_ID
                         WHERE       m1.p_network_site_status_id = 4
                                 AND NVL (m1.valid_to, SYSDATE + 1) > SYSDATE
                    ";

                DataTable dt = dbl.GetDataTable(sxQuery);//, lsParams.ToArray());

                AddToCache(sxCacheKey, dt, CacheType.Daily);

            }
            return (DataTable)HttpRuntime.Cache[sxCacheKey];
        }        
		
        public override String GetCacheFilename()
        {
            return KCache.Filename.COMPANY_REPORT_VIEWS;
        }
    }
}